package com.jr.dao.impl;

import com.jr.dao.SupplierWhiteListDao;
import com.jr.entry.SupplierWhiteList;
import com.jr.pojo.AddSupplierWhiteList;
import com.jr.pojo.InformationInWhiteMess;
import com.jr.util.DBHelper;
import com.jr.util.WhitePageHelper;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;

public class SupplierWhiteListDaoImpl  implements SupplierWhiteListDao {
    String sql ;
    DBHelper db = new DBHelper();
    @Override
    public boolean deleteSupplierWhiteListByEnterpriseIdAndSupplierId(String enterpriseId, String supplierId) {
         sql = "delete from supplier_white_list where enterpriseId = ? and supplierId = ?";
        return db.updateTable(sql,enterpriseId,supplierId)!=0?true:false;
    }

    @Override
    public boolean insertSupplierWhiteList(SupplierWhiteList supplierWhiteList) {
       sql = "insert into supplier_white_list values(null,?,?,?,?) ";
        LocalDate today = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String dateString = today.format(formatter);
       int i = db.updateTable(sql,supplierWhiteList.getEnterpriseId(),supplierWhiteList.getSupplierId(),supplierWhiteList.getSupplierLevel(),dateString);
        return i!=0?true:false;
    }


    @Override
    public ArrayList<InformationInWhiteMess> selectSupplierWhiteListByAccount(String account, String level, String name, WhitePageHelper whitePageHelper) {
        ArrayList list = new ArrayList();
        list.add(account);
        StringBuilder sql = new StringBuilder("  SELECT " +
                "                    e2.name AS name, " +
                "                    u2.idcardName AS idcardName, " +
                "                    u2.phone AS phone, " +
                "                    e2.email AS email, " +
                "                    w.supplierLevel AS supplierLevel, " +
                "                    w.updateDate " +
                "                FROM " +
                "                    user u" +
                "                JOIN " +
                "                    enterprise e1 ON u.enterpriseId = e1.id " +
                "                JOIN " +
                "                    supplier_white_list w ON w.enterpriseId = e1.id " +
                "                JOIN " +
                "                    enterprise e2 ON w.supplierId = e2.id " +
                "                JOIN " +
                "                    user u2 ON e2.id = u2.enterpriseId " +
                "                WHERE " +
                "                    u.account = ? ");
        if(level!=null && level!="") {
            sql.append(" AND w.supplierLevel= ? ");
            list.add(level);
        }
        if(name!=null && name!=""){
            sql.append(" AND e2.name LIKE ? ");
            list.add("%"+name+"%");
        }
        sql.append(" limit ? , ? ");
        int startIndex=(whitePageHelper.getIndexPage()-1)*whitePageHelper.getShowData();
        list.add(startIndex);
        list.add(whitePageHelper.getShowData());
        return db.query(sql.toString(), InformationInWhiteMess.class,list.toArray());
     }
    @Override
    public boolean updateSupplierWhiteListLevel(String level,String account,String name) {
        sql = "UPDATE supplier_white_list w\n" +
                "SET w.supplierLevel = ? \n" +
                "WHERE w.enterpriseId IN (\n" +
                "    SELECT u.enterpriseId\n" +
                "    FROM user u\n" +
                "    WHERE u.account = ? \n" +
                ")\n" +
                "AND w.supplierId IN (\n" +
                "    SELECT e.id\n" +
                "    FROM enterprise e\n" +
                "    WHERE e.name = ? \n" +
                ")";
        return db.updateTable(sql,level,account,name)!=0?true:false;
    }

    @Override
    public ArrayList<AddSupplierWhiteList> selectSupplier(String account) {
        sql = "SELECT e.name, e.socialUniformCode\n" +
                "FROM enterprise e\n" +
                "WHERE e.id NOT IN (\n" +
                "    SELECT supplierId \n" +
                "    FROM supplier_white_list w \n" +
                "    JOIN user u ON w.enterpriseId = u.enterpriseId\n" +
                "    WHERE u.account = ?\n" +
                "    \n" +
                "    UNION\n" +
                "\n" +
                "    SELECT supplierId \n" +
                "    FROM supplier_black_list b \n" +
                "    JOIN user u ON b.enterpriseId = u.enterpriseId \n" +
                "    WHERE u.account = ?\n" +
                "    \n" +
                "    UNION\n" +
                "\n" +
                "    SELECT enterpriseId " +
                "    FROM user " +
                "    WHERE account = ?" +
                ");";
        return db.query(sql,AddSupplierWhiteList.class,account,account,account);
    }

    @Override
    public int selectSize(String account, String level, String name) {
        ArrayList list = new ArrayList();
        list.add(account);
        StringBuilder sql = new StringBuilder("  SELECT " +
                "                    e2.name AS name, " +
                "                    u2.idcardName AS idcardName, " +
                "                    u2.phone AS phone, " +
                "                    e2.email AS email, " +
                "                    w.supplierLevel AS supplierLevel, " +
                "                    w.updateDate " +
                "                FROM " +
                "                    user u" +
                "                JOIN " +
                "                    enterprise e1 ON u.enterpriseId = e1.id " +
                "                JOIN " +
                "                    supplier_white_list w ON w.enterpriseId = e1.id " +
                "                JOIN " +
                "                    enterprise e2 ON w.supplierId = e2.id " +
                "                JOIN " +
                "                    user u2 ON e2.id = u2.enterpriseId " +
                "                WHERE " +
                "                    u.account = ? ");
        if(level!=null && level!="") {
            sql.append(" AND w.supplierLevel= ? ");
            list.add(level);
        }
        if(name!=null&& name!="") {
            sql.append(" AND e2.name LIKE ? ");
            list.add("%"+name+"%");
        }
        return db.query(sql.toString(), InformationInWhiteMess.class,list.toArray()).size();
    }


}
